﻿create procedure [dbo].[spXacNhanGiuCho]
@MaGiuCho int,
@MaChuyenBayDi int,
@MaChuyenBayVe int
as
declare
@MaChuyenBay int,
@SLGiuCho int,
@MaHangGhe int,
@SLGhe int
begin
	
	--Kiểm tra xem MaGiuCho có tồn tại không
	Set @SLGiuCho = (select count(*) from DBGiuCho where MaGiuCho = @MaGiuCho and TinhTrang = 'da giu cho')
	if @SLGiuCho = 0
	begin
		return 0
	end
	-- Bắt đầu transaction
	begin tran
		declare curChuyenBay cursor for select MaChuyenBay from ChuyenBayGiuCho where MaChuyenBay != @MaChuyenBayDi and MaChuyenBay != @MaChuyenBayVe
		open curChuyenBay
		fetch next from curChuyenBay into  @MaChuyenBay
		While (@@FETCH_STATUS = 0)
		begin
			--Giải phóng các vị trí đã giữ chổ trong chuyến bay đó
			declare curHangGhe cursor for select MaHangGhe, SoLuong from CTGiuChoHangGhe where MaGiuCho =  @MaGiuCho
			open curHangGhe
				fetch next from curHangGhe into @MaHangGhe, @SLGhe
				While (@@FETCH_STATUS = 0)
				begin
					update DanhSachHangGhe set SoViTriConTrong = SoViTriConTrong + @SLGhe where MaChuyenBay = @MaChuyenBay and MaHangGhe = @MaHangGhe
					if @@ERROR <> 0
					begin
						close curHangGhe
						close curChuyenBay
						deallocate curHangGhe
						deallocate curChuyenBay
						rollback tran
						return 0
					end
					fetch next from curHangGhe into @MaHangGhe, @SLGhe
				end
			close curHangGhe
			deallocate curHangGhe
			-- Xóa chuyến bay đó trong chuyến bay giữ chổ
			Delete ChuyenBayGiuCho where MaChuyenBay = @MaChuyenBay and MaGiuCho = @MaGiuCho
			if @@ERROR <> 0
			begin	
				close curChuyenBay
				deallocate curChuyenBay
				rollback tran
				return 0
			end
			fetch next from curChuyenBay into  @MaChuyenBay
		end
		close curChuyenBay
		deallocate curChuyenBay
	update DBGiuCho set TinhTrang = 'da xac nhan' where MaGiuCho = @MaGiuCho
	commit tran
	return 1
end


